<?php
/**
 * Author:xsc
 * Time:2023/9/4 17:07
 */

namespace App\Repository;

use App\Models\Goods;
use Illuminate\Support\Facades\DB;

class GoodsRepo extends BaseRepo
{

    /**
     * 根据藏品id查询商品数量
     * @param int $special_id
     * @return array
     * Author:xsc
     * Time:2023/9/4 17:09
     */
    public function getCountBySpecialId(int $special_id)
    {
        return Goods::query()->where('special_id', $special_id)->count();
    }

    /**
     * 根据goods_id获取未被使用的商品
     * @param int $goods_id
     * @return array
     * Author:xsc
     * Time:2023/9/6 15:04
     */
    public function getUnusedGoods(int $goods_id)
    {
        $res = Goods::query()->where('id', $goods_id)->where('member_id', 0)->first();
        return $res ? $res->toArray() : [];
    }

    /**
     * 新增/更新商品
     * @param array $data
     * @param int $id
     * @return int
     * Author:xsc
     * Time:2023/9/6 15:58
     */
    public function save(array $data, int $id = 0)
    {
        if ($id) {
            return Goods::query()->where('id', $id)->update($data);
        } else {
            return Goods::query()->insertGetId($data);
        }
    }

    /**
     * 更新商品销量
     * @param int $id
     * @param int $num
     * @return int
     * Author:xsc
     * Time:2023/9/6 16:33
     */
    public function updateSaleCount(int $id, int $num = 1)
    {
        return Goods::query()->where('id', $id)->increment('sale_count', $num);
    }

    /**
     * 获取商品数量
     * @param array $params
     * @return int
     * Author:xsc
     * Time:2023/10/9 14:07
     */
    public function getGoodsCount(array $params)
    {
        $where = [
            ['g.type', '=', 1],
            ['g.channel', '=', 2],
            //['s.type', '=', 9],
            //['s.status', '=', 1],
            //['s.apply_status', '=', 2],
            ['s.cast_status', '=', 3],
        ];
        if (isset($params['member_id'])) {
            $where[] = ['g.member_id', '=', $params['member_id']];
        }
        if (isset($params['sell_status'])) {
            $where[] = ['g.sell_status', '=', $params['sell_status']];
        }
        if (isset($params['merchants_id'])) {
            $where[] = ['s.merchants_id', '=', $params['merchants_id']];
        }
        if (isset($params['special_id'])) {
            $where[] = ['s.id', '=', $params['special_id']];
        }

        // 转赠时间限制
        $transfer_time = ConfigRepo::getInstance()->getConfigValue('transfer_time');
        $time_limit = date('Y-m-d H:i:s', time() - $transfer_time * 60 * 60);
        $raw = "if(`think_g`.`stand_time` is not null,`think_g`.`stand_time` < '" . $time_limit . "',`think_g`.`create_time` < '" . $time_limit . "')";

        $res = Goods::query()
            ->from('goods', 'g')
            ->join('special as s', 'g.special_id', '=', 's.id')
            ->join('special_album as a', 's.special_album_id', '=', 'a.id')
            ->where($where)
            ->whereRaw($raw)
            ->count();
        return $res;
    }

    /**
     * 获取商品列表
     * @param array $params
     * @return array
     * Author:xsc
     * Time:2023/10/9 14:08
     */
    public function getGoodsList(array $params)
    {
        $fields = [
            'g.id as goods_id', 'g.code', 'g.type', 'g.price', 'g.status', 'g.chain_mark', 'g.sell_status', 'g.create_time', 'g.stand_time',
            's.id as special_id', 's.type as special_type', 's.title', 's.thumb', 's.ca_cast_count', 'a.name as album_name',
        ];
        $where = [
            ['g.type', '=', 1],
            ['g.channel', '=', 2],
            //['s.type', '=', 9],
            //['s.status', '=', 1],
            //['s.apply_status', '=', 2],
            ['s.cast_status', '=', 3],
        ];
        $page = isset($params['page']) ? $params['page'] : 1;
        $page_size = isset($params['page_size']) ? $params['page_size'] : 20;
        if (isset($params['member_id'])) {
            $where[] = ['g.member_id', '=', $params['member_id']];
        }
        if (isset($params['sell_status'])) {
            $where[] = ['g.sell_status', '=', $params['sell_status']];
        }
        if (isset($params['merchants_id'])) {
            $where[] = ['s.merchants_id', '=', $params['merchants_id']];
        }
        if (isset($params['special_id'])) {
            $where[] = ['s.id', '=', $params['special_id']];
        }

        // 转赠时间限制
        $transfer_time = ConfigRepo::getInstance()->getConfigValue('transfer_time');
        $time_limit = date('Y-m-d H:i:s', time() - $transfer_time * 60 * 60);
        $raw = "if(`think_g`.`stand_time` is not null,`think_g`.`stand_time` < '" . $time_limit . "',`think_g`.`create_time` < '" . $time_limit . "')";

        $res = Goods::query()
            ->from('goods', 'g')
            ->join('special as s', 'g.special_id', '=', 's.id')
            ->join('special_album as a', 's.special_album_id', '=', 'a.id')
            ->where($where)
            ->whereRaw($raw)
            ->orderBy('g.id', 'desc')
            ->forPage($page, $page_size)
            ->get($fields)
            ->toArray();
        return $res;
    }

    /**
     * 获取可用的商品列表
     * @param array $params
     * @param int $type
     * @param int $need_count
     * @return array
     * Author:xsc
     * Time:2023/10/18 17:34
     */
    public function getUsableGoodsList(array $params, int $type = 1, int $need_count = 0)
    {
        // 转赠时间限制
        $transfer_time = ConfigRepo::getInstance()->getConfigValue('transfer_time');
        $time_limit = date('Y-m-d H:i:s', time() - $transfer_time * 60 * 60);

        $fields = " g.id goods_id,g.code,g.type,g.price,g.status,g.chain_mark,g.sell_status,g.create_time,g.stand_time,
        s.id special_id,s.type special_type,s.title,s.thumb,s.base_price,s.ca_cast_count,s.create_time special_create_time,
        i.user_name,i.portrait,sa.name album_name ";

        if ($type == 1) {
            if ($need_count) {
                $fields = " count(1) cnt ";
            }
        } else if ($type == 2) {
            $fields .= " ,count(1) cnt ";
        }

        $member_id = $params['member_id'];

        $common_where = " g.member_id = " . $member_id . " and g.type = 1 and g.channel = 2 and g.sell_status = 0 and g.status <> 7 ";

        $where = $common_where;
        if (isset($params['merchants_id'])) {
            $where .= " and s.merchants_id = " . $params['merchants_id'];
        }
        if (isset($params['special_id'])) {
            $where .= " and s.id = " . $params['special_id'];
        }
        if (isset($params['title'])) {
            $where .= " and s.title like '%" . $params['title'] . "%'";
        }

        $a = "(select g.special_id,count(distinct sp.id) cnt1 from think_goods g
    left join think_special_privilege sp on g.special_id = sp.special_id where " . $common_where . " group by g.special_id) a ";

        $b = "(select g.special_id,spo.goods_id,count(distinct spo.id) cnt2 from think_goods g
    left join think_special_privilege_order spo on g.special_id = spo.special_id and g.id = spo.goods_id where " . $common_where . " and spo.status = 1 group by spo.goods_id) b ";

        $c = "(select g.special_id,spuo.goods_id,count(distinct spuo.id) as cnt3 from think_goods g
    left join think_special_privilege_ugc_order spuo on g.special_id = spuo.special_id and g.id = spuo.goods_id where " . $common_where . " and spuo.status not in (2,5,6) group by spuo.goods_id) c ";

        $sql = "select " . $fields . " from think_goods g
        inner join think_special s on g.special_id = s.id
        inner join think_merchants_info i on s.merchants_id = i.mer_id
        inner join think_special_album sa on s.special_album_id = sa.id
        left join " . $a . " on g.special_id = a.special_id
        left join " . $b . " on g.special_id = b.special_id and g.id = b.goods_id
        left join " . $c . " on g.special_id = c.special_id and g.id = c.goods_id
        where " . $where . "
        and (a.cnt1 = 0 or a.cnt1 = b.cnt2)
        and (c.cnt3 is null or c.cnt3 = 0)
        and if(g.stand_time is not null,g.stand_time < '" . $time_limit . "',g.create_time < '" . $time_limit . "')";

        if ($type == 1) {
            $sql .= " order by g.id desc ";
        } else if ($type == 2) {
            $sql .= " group by g.special_id order by g.special_id desc";

            if ($need_count) {
                $sql = "select count(1) cnt from ( " . $sql . " ) aaa";
            }
        }

        if (!$need_count) {
            $page = isset($params['page']) ? $params['page'] : 1;
            $page_size = isset($params['page_size']) ? $params['page_size'] : 20;
            $limit = ($page - 1) * $page_size;
            $sql .= " limit " . $limit . "," . $page_size;
        }

        $res = DB::select($sql);
        if ($need_count) {
            return $res[0]->cnt;
        }
        return $res;
    }

    /**
     * 获取商品信息
     * @param int $goods_id
     * @param int $member_id
     * @param int $merchants_id
     * @return array
     * Author:xsc
     * Time:2023/10/9 15:59
     */
    public function getInfo(int $goods_id, int $member_id = 0, int $merchants_id = 0)
    {
        $fields = [
            'g.id as goods_id', 'g.code', 'g.type', 'g.member_id', 'g.price', 'g.status', 'g.chain_mark', 'g.sell_status', 'g.create_time', 'g.stand_time', 'g.channel',
            's.id as special_id', 's.type as special_type', 's.title', 's.thumb', 's.ca_cast_count',
            'a.name as album_name',
        ];
        $where = [
            ['g.id', '=', $goods_id],
            //['s.type', '=', 9],
            //['s.status', '=', 1],
            //['s.apply_status', '=', 2],
            ['s.cast_status', '=', 3],
        ];
        if ($member_id) {
            $where[] = ['g.member_id', '=', $member_id];
        }
        if ($merchants_id) {
            $where[] = ['s.merchants_id', '=', $merchants_id];
        }
        $res = Goods::query()
            ->from('goods', 'g')
            ->join('special as s', 'g.special_id', '=', 's.id')
            ->join('special_album as a', 's.special_album_id', '=', 'a.id')
            ->where($where)
            ->first($fields);
        return $res ? $res->toArray() : [];
    }

    /**
     * 根据藏品id,获取已领取商品的用户
     * @param int $special_id
     * @return array
     * Author:xsc
     * Time:2023/11/6 11:05
     */
    public function getListBySpecialId(int $special_id)
    {
        $where = [
            ['special_id', '=', $special_id],
            ['member_id', '>', 0],
        ];
        return Goods::query()->where($where)->get()->toArray();
    }

}
